package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.ConferenceContent;
import com.realtyManage.springboot.Model.Do.Coupon;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.beans.Transient;
import java.util.List;

/**
 *优惠劵
 *
 * @author 杭州素色网络科技有限公司
 * @since 2019-10-03
 */

@Repository
public interface CouponRepository extends JpaRepository<Coupon, Integer>, JpaSpecificationExecutor<Coupon> {

    @Modifying
    @Transactional
    @Query("update Coupon a set " +
            "a.img = CASE WHEN :#{#coupon.img} IS NULL THEN a.img ELSE :#{#coupon.img} END ," +
            "a.couponDetail = CASE WHEN :#{#coupon.couponDetail} IS NULL THEN a.couponDetail ELSE :#{#coupon.couponDetail} END ," +
            "a.couponValue = CASE WHEN :#{#coupon.couponValue} IS NULL THEN a.couponValue ELSE :#{#coupon.couponValue} END ," +
            "a.infoTime = CASE WHEN :#{#coupon.infoTime} IS NULL THEN a.infoTime ELSE :#{#coupon.infoTime} END ," +
            "a.endTime = CASE WHEN :#{#coupon.endTime} IS NULL THEN a.endTime ELSE :#{#coupon.endTime} END ," +
            "a.phone = CASE WHEN :#{#coupon.phone} IS NULL THEN a.phone ELSE :#{#coupon.phone} END ," +
            "a.num = CASE WHEN :#{#coupon.num} IS NULL THEN a.num ELSE :#{#coupon.num} END ," +
            "a.saleId = CASE WHEN :#{#coupon.saleId} IS NULL THEN a.saleId ELSE :#{#coupon.saleId} END " +
            "where a.id = :#{#coupon.id}")
    int updateCoupon(@Param("coupon") Coupon coupon);

    @Modifying
    @Transactional
    @Query(value = "UPDATE Coupon SET saleId = ?1 WHERE id IN ?2")
    public int distributeCoupon(String saleId,Integer id);

    @Query(value = "SELECT * FROM coupon WHERE sale_id IS NULL AND user_id = ?1 and end_time >= ?2",nativeQuery = true)
    List<Coupon> findCoupon(String userId,String time);

    @Query(value = "SELECT * FROM coupon WHERE sale_id = ?1 and end_time >= ?2 ORDER BY phone ASC",nativeQuery = true)
    List<Coupon> findSaleCoupon(String userId,String time);

    @Modifying
    @Transactional
    @Query(value = "UPDATE Coupon SET phone = ?1 WHERE code = ?2")
    public int grantCoupon(String phone,String couponCode);

    Coupon findByCode(String code);

    @Query(value = "SELECT * FROM coupon WHERE phone = ?1 and end_time < ?2 ORDER BY phone ASC",nativeQuery = true)
    List<Coupon> findSaleExpireCoupon(String phone,String time);

    @Query(value = "SELECT * FROM coupon WHERE phone = ?1 and end_time >= ?2 ORDER BY phone ASC",nativeQuery = true)
    List<Coupon> findMyCoupon(String phone,String time);


    @Modifying
    @Transactional
    @Query(value = "delete from coupon where  id in (?1)",nativeQuery = true)
    int deleteCoupon(List id);


    @Query(value = "SELECT * FROM coupon WHERE id = ?1",nativeQuery = true)
    Coupon findCouponByIdDetail(String id);

    @Query(value = "SELECT * FROM coupon WHERE end_time >= NOW() and distribute_type = 0 ORDER BY end_time DESC",nativeQuery = true)
    List<Coupon> queryWCoupon();

    @Query(value = "SELECT COUNT(1) FROM exchange_coupon WHERE user_id = ?1 AND coupon_id= ?2 ",nativeQuery = true)
    int countNum(Integer uid,Integer couponId);

}
